IF OBJECT_ID('dbo.RptSubContractDeliverableDetailReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubContractDeliverableDetailReport
    IF OBJECT_ID('dbo.RptSubContractDeliverableDetailReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubContractDeliverableDetailReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubContractDeliverableDetailReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptSubContractDeliverableDetailReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/03/2008
--
-- Description: Gets Raw data of SubDeliverables
--
-- Serves as a drill down to the Report, RptSubContractDeliverableReport
--
-- Parameters:  
--     @StartDate                         
--     @EndDate                           
--     @DeliverableStatus                 
--     @Institution                       
--     @Department                        
--     @Unit                              
--     @Subcontractor                     
--     @SubcontractSpecialist             
--     @DomainUserId                      
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
--EXECUTE dbo.RptSubContractDeliverableDetailReport
--                       @StartDate               = '6/1/2008',
--                       @EndDate                 = '8/31/2008',
--					   @DeliverableType			= NULL,
--                       @DeliverableStatus		='FullyExecuted',
--                       @Institution             = NULL, 
--                       @Department              = NULL, 
--                       @Unit                    = NULL, 
--					   @ChiefCode               = NULL,
--					   @FundNumber				= NULL,
--					   @PrincipalInvestigator	= NULL,
--					   @OriginatingSponsor		= NULL,
--					   @Sponsor					= NULL,
--                       @Subcontractor           = NULL, 
--                       @SubcontractSpecialist   = NULL, 					  
--					   @ContractManager = '14CFDD43-641C-416B-A',  
--                       @DomainUserId            = 11055
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/03/08    KC        Created
-- 10/09/08    KC        Removed DeliverableType as a Param.
--                       changed to use Fct table.
-- 10/10/08    KC        Added ChiefCode as a Param
-- 10/13/08    KC        Finalized @SubcontractSpecialist search
-- 11/12/08    KC        Added @DeliverableType
-- 11/13/08    KC        Added Security and new GroupBy Value (SC)
-- 11/20/08    KC        Added SponsorTypeName to ResultSet
-- 11/25/08    KC        Added two more Statuses
-- 12/01/08    KC        Added two new REPORTCATs
-- 12/02/08    KC        Broke out PSA-IN/OUT and Added All-SubIn/Out
-- 12/08/08    KC        Added @FundNumber and @PrincipalInvestigator
-- 12/09/08    KC        Added @OriginatingSponsor and @Sponsor
--                       DaysCalc also changed
-- 12/10/08    KC        Added WITH RECOMPILE, use TempTable now
--                       N/A for SubContractor for SubIn
-- 12/11/08    KC        Changes to Subcontractor, including parsing.
-- 12/15/08    KC        Made Deliverables join to latest status and outer
--                       join because "Pending Out Years" does not update
--                       any of the Processed nor Status dates
-- 12/16/08    KC        Changed ColType for DaysFromDueToCompleted to VARCHAR(5)
-- 12/17/08    KC        New Security function
-- 12/18/08    KC        Processed date is now populated for Pending Out years
--                       revert to old code for Deliverables latest
-- 12/26/08    KC        All-SubIn and Only-SubIn were reversed (and out) fixed.
-- 12/29/08    KC        Changed DeliverableType param values, for display in Rpt.
--                       Added TempTable, #tt_Agreements for Performance
-- 01/07/09    KC        Increased size of Comments field in Temp table to 2000
-- 03/13/2009  jml29	 Service Center Ticket#3036559 - switch IndirectExpenses and DirectExpenses column values.
--
-- =======================================================================
CREATE PROCEDURE dbo.RptSubContractDeliverableDetailReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @DeliverableType                   VARCHAR(200) = NULL,
     @DeliverableStatus                 VARCHAR(100) = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @ChiefCode                         VARCHAR(4)   = NULL,
     @FundNumber                        VARCHAR(1000) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL, 
     @OriginatingSponsor                VARCHAR(100) = NULL,
     @Sponsor                           VARCHAR(100) = NULL,
     @Subcontractor                     VARCHAR(200) = NULL,
     @SubcontractSpecialist             VARCHAR(100) = NULL,
	 @ContractManager					VARCHAR(100) = NULL,
     @DomainUserId                      INT
     ) 
WITH RECOMPILE                                          
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
			   @DeliverableStatusCodes	VARCHAR(5000)

     -- Need to break the filter out to multiple steps, so need a temp table here
     DECLARE   @Subcontracts TABLE (
                    FolderNumber             VARCHAR(12)    NULL,
                    InfoEdPropNumber         VARCHAR(50)    NULL,
                    Institution              VARCHAR(50)    NULL,
                    Department               VARCHAR(50)    NULL,
                    Unit                     VARCHAR(50)    NULL,
                    ChiefCode                VARCHAR(4)     NULL,
                    SponsorName              VARCHAR(75)    NULL,
                    SponsorTypeName          VARCHAR(50)    NULL,
                    SecondarySponsor         VARCHAR(75)    NULL,
                    ProposalShortTitle       VARCHAR(64)    NULL,
                    PrincipalInvestigator    VARCHAR(64)    NULL,
                    ProjectStartDate         DATETIME       NULL,
                    ProjectEndDate           DATETIME       NULL,
                    InstrumentType           VARCHAR(50)    NULL,
                    SubContractorName        VARCHAR(75)    NULL,
                    BudgetProjectStartDate   DATETIME       NULL,
                    BudgetProjectEndDate     DATETIME       NULL,
                    ProjectAwardedAmount     MONEY          NULL,
                    BudgetStartDate          DATETIME       NULL,
                    BudgetEndDate            DATETIME       NULL,
                    BudgetFundNumber         VARCHAR(20)    NULL,
                    ExpenseAmount            MONEY          NULL,
                    IndirectExpenses         MONEY          NULL,
                    DirectExpenses           MONEY          NULL,
                    CashBalance              MONEY          NULL,
                    ReportId                 BIGINT         NULL,
                    ReportName               VARCHAR(50)    NULL,
                    REPORTCAT                VARCHAR(80)    NULL,
                    REPORTSTAT               VARCHAR(80)    NULL,
                    SubmittedDate            DATETIME       NULL,
                    DueDate                  DATETIME       NULL,
                    StatusDate               DATETIME       NULL,
                    CompletedDate            DATETIME       NULL,
                    DaysFromDueToCompleted   VARCHAR(5)     NULL,
                    SubmittedBy              VARCHAR(50)    NULL,
                    StatusBy                 VARCHAR(50)    NULL,
                    CompletedBy              VARCHAR(50)    NULL,
                    PeriodStartDate          DATETIME       NULL,
                    PeriodEndDate            DATETIME       NULL,
                    DeliverableComments      VARCHAR(2000)  NULL,
                    REPORTCAT_CODE           VARCHAR(50)    NULL,
                    REPORTSTAT_CODE          VARCHAR(50)    NULL,
                    AgreementId              INT            NULL,
                    InstitutionId            INT            NULL,
                    DepartmentId             INT            NULL,
                    UnitId                   INT            NULL,
                    SponsorId                INT            NULL,
                    PrincipalInvestigatorId  INT            NULL,
                    SubcontractsYN           CHAR(1)        NULL,
                    SubContractorId          INT            NULL,
                    SubOutContractorId       INT            NULL, 
					ContractSpecialistID	 VARCHAR(100)   NULL,
                    ContractSpecialist		 VARCHAR(60)	NULL)

     -- ALL checking
     IF @DeliverableStatus IN ('ALL','','0') SET @DeliverableStatus = NULL
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @ChiefCode IN ('ALL','','0') SET @ChiefCode = NULL
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @OriginatingSponsor IN ('ALL','','0') SET @OriginatingSponsor = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @Subcontractor IN ('ALL','','0') SET @Subcontractor = NULL
     IF @SubcontractSpecialist IN ('ALL','','0') SET @SubcontractSpecialist = NULL
     IF @ContractManager IN ('ALL','','0') SET @ContractManager = NULL

     -- Verify firt.
     IF @DeliverableType IS NOT NULL AND @DeliverableType NOT IN ('ALL','AllSubsIn','AllSubsOut','Only-SubIn','Only-SubOut','SubIn-MOD','SubOut-MOD','PSA-IN','PSA-OUT')
     BEGIN
          RAISERROR ('ERROR! DeliverableType has an invalid value(%s). Set to a valid DeliverableType and try again.',11,1,@DeliverableType) 
          RETURN -1
     END
               
     -- Set Deliverable Type
     IF @DeliverableType IS NOT NULL
          SELECT @DeliverableType = CASE @DeliverableType 
                              WHEN 'AllSubsIn' THEN 'REPORTCAT_13,REPORTCAT_40,REPORTCAT_43'
                              WHEN 'AllSubsOut' THEN 'REPORTCAT_14,REPORTCAT_39,REPORTCAT_44,REPORTSTAT_46'
                              WHEN 'Only-SubIn' THEN 'REPORTCAT_13'         
                              WHEN 'Only-SubOut' THEN 'REPORTCAT_14'        
                              WHEN 'SubIn-MOD' THEN 'REPORTCAT_43'
                              WHEN 'SubOut-MOD' THEN 'REPORTCAT_44'
                              WHEN 'PSA-IN' THEN 'REPORTCAT_40'
                              WHEN 'PSA-OUT' THEN 'REPORTCAT_39'
                              ELSE 'REPORTCAT_13,REPORTCAT_40,REPORTCAT_14,REPORTCAT_39,REPORTCAT_43,REPORTCAT_44,REPORTCAT_47,REPORTSTAT_46'   -- ALL
                           END

     -- If drilling down to a single status, set the codes here
        SELECT    @DeliverableStatusCodes = 'ERROR'  

								IF @DeliverableStatus IS NULL
								BEGIN
										SELECT    @DeliverableStatusCodes =  @DeliverableStatusCodes +',REPORTSTAT_40,REPORTSTAT_19,REPORTSTAT_28,REPORTSTAT_24,' + 
												  'REPORTSTAT_33,REPORTSTAT_20,REPORTSTAT_29,REPORTSTAT_16,REPORTSTAT_25,' +
												  'REPORTSTAT_17,REPORTSTAT_26,REPORTSTAT_18,REPORTSTAT_27,REPORTSTAT_21,' +
                                                  'REPORTSTAT_30,REPORTSTAT_22,REPORTSTAT_31,REPORTSTAT_23,REPORTSTAT_32,' +
                                                  'REPORTSTAT_34,REPORTSTAT_35,REPORTSTAT_46'
								END
								ELSE
								BEGIN			  
											--IF @DeliverableStatus IS NOT NULL
          
											SELECT   @DeliverableStatusCodes = CASE   WHEN @DeliverableStatus='UnExecutedAgreementRecd' THEN @DeliverableStatusCodes + ',REPORTSTAT_40'  ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE   WHEN @DeliverableStatus LIKE '%Sub In - Unexecuted Agreement Received%' THEN @DeliverableStatusCodes + ',REPORTSTAT_40'  ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='PendingGA' THEN @DeliverableStatusCodes + ',REPORTSTAT_19,REPORTSTAT_28'  ELSE @DeliverableStatusCodes END 
										    SELECT   @DeliverableStatusCodes = CASE  WHEN @DeliverableStatus LIKE '%Sub In - Pending GA%' THEN @DeliverableStatusCodes + ',REPORTSTAT_19'  ELSE @DeliverableStatusCodes END 
										    SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Pending GA%' THEN @DeliverableStatusCodes + ',REPORTSTAT_28' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='PendingDocFromDeptPI' THEN @DeliverableStatusCodes + ',REPORTSTAT_24,REPORTSTAT_33' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Pending Docs from Dept./PI%' THEN @DeliverableStatusCodes + ',REPORTSTAT_24' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Pending Docs from Dept./PI%' THEN @DeliverableStatusCodes + ',REPORTSTAT_33' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='PendingDeptPIAppr' THEN @DeliverableStatusCodes + ',REPORTSTAT_20,REPORTSTAT_29' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Pending Dept./PI Approval%' THEN @DeliverableStatusCodes + ',REPORTSTAT_20' ELSE @DeliverableStatusCodes END
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Pending Dept./PI Approval%' THEN @DeliverableStatusCodes + ',REPORTSTAT_29' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='InQueue' THEN @DeliverableStatusCodes + ',REPORTSTAT_16,REPORTSTAT_25' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - In Queue%' THEN @DeliverableStatusCodes + ',REPORTSTAT_16' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - In Queue%' THEN @DeliverableStatusCodes + ',REPORTSTAT_25' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='Assigned' THEN @DeliverableStatusCodes + ',REPORTSTAT_17,REPORTSTAT_26' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Assigned%' THEN @DeliverableStatusCodes + ',REPORTSTAT_17' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Assigned%' THEN @DeliverableStatusCodes + ',REPORTSTAT_26' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='InNegotiation' THEN @DeliverableStatusCodes + ',REPORTSTAT_18,REPORTSTAT_27' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - In Negotiation%' THEN @DeliverableStatusCodes + ',REPORTSTAT_18' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - In Negotiation%' THEN @DeliverableStatusCodes + ',REPORTSTAT_27' ELSE @DeliverableStatusCodes END             
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='PartiallyExecuted' THEN @DeliverableStatusCodes + ',REPORTSTAT_21,REPORTSTAT_30' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Partially Executed%' THEN @DeliverableStatusCodes + ',REPORTSTAT_21' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Partially Executed%' THEN @DeliverableStatusCodes + ',REPORTSTAT_30' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='FullyExecuted' THEN @DeliverableStatusCodes + ',REPORTSTAT_22,REPORTSTAT_31' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Fully Executed%' THEN @DeliverableStatusCodes + ',REPORTSTAT_22' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Fully Executed%' THEN @DeliverableStatusCodes + ',REPORTSTAT_31' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='OtherUnresolved' THEN @DeliverableStatusCodes + ',REPORTSTAT_23,REPORTSTAT_32' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Other/UnResolved%' THEN @DeliverableStatusCodes + ',REPORTSTAT_23' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Other/UnResolved%' THEN @DeliverableStatusCodes + ',REPORTSTAT_32' ELSE @DeliverableStatusCodes END 
                                            SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='PendingOutYears' THEN @DeliverableStatusCodes + ',REPORTSTAT_34,REPORTSTAT_35' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub In - Pending Out Yr%' THEN @DeliverableStatusCodes + ',REPORTSTAT_34' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Pending Out Yr%' THEN @DeliverableStatusCodes + ',REPORTSTAT_35' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus='Requested' THEN @DeliverableStatusCodes + ',REPORTSTAT_46' ELSE @DeliverableStatusCodes END 
											SELECT   @DeliverableStatusCodes = CASE WHEN @DeliverableStatus LIKE '%Sub Out - Requested%' THEN @DeliverableStatusCodes + ',REPORTSTAT_46' ELSE @DeliverableStatusCodes END 
									END
 

     SELECT @DeliverableStatusCodes = replace(@DeliverableStatusCodes,'ERROR,','') 
     -- Check for Errors
     IF @DeliverableStatus = 'ERROR'
     BEGIN
          RAISERROR ('ERROR! DeliverableStatus has an invalid value. Set to a valid StatusColumn name.',11,1,@DeliverableStatus) 
          RETURN -1
     END
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

	 
	 CREATE CLUSTERED INDEX IC_#tt_Agreements ON #tt_Agreements  
	 (  
	  AgreementId ASC  
	 )  

--     SELECT  ReportId, MIN(ProcessedDate) AS LastDate  ,REPORTSTAT
--     INTO #tmpSubContractDeliverables      
--     FROM    dbo.vwInfoEdSubContractDeliverables  
--     GROUP BY ReportId  ,REPORTSTAT
     SELECT  ReportId, MAX(ProcessedDate) AS LastDate
     INTO #tmpSubContractDeliverables    
     FROM    dbo.vwInfoEdSubContractDeliverables
     GROUP BY ReportId

	 CREATE CLUSTERED INDEX IC_#tmpSubContractDeliverables ON #tmpSubContractDeliverables  
	 (  
	  ReportId ASC,  
	  LastDate ASC
	  )  

	SELECT D.prop_no,
            D.ReportId,
            D.ReportName,
            D.REPORTCAT_CODE,
            D.REPORTCAT,
            D.REPORTSTAT,
            D.REPORTSTAT_CODE,
            D.DueDate,
            D.SubmittedDate,
            D.ProcessedDate AS StatusDate,
            D.CompletedDate,
            D.SubmittedBy,
            D.StatusBy,
            D.CompletedBy,
            D.PeriodStartDate,
            D.PeriodEndDate,
            D.Comments,
            D.DaysComplCalc
    INTO #tmpDEL  
    FROM    dbo.vwInfoEdSubContractDeliverables D
            INNER JOIN (SELECT  ReportId,LastDate
                        FROM    dbo.#tmpSubContractDeliverables) LAST 
            ON D.ReportId = LAST.ReportId AND D.ProcessedDate = LAST.LastDate
	     
    
     -- Result Set into temp table
     INSERT    @Subcontracts
                    (FolderNumber,
                    InfoEdPropNumber,
                    Institution,
                    Department,
                    Unit,
                    ChiefCode,
                    SponsorName,
                    SponsorTypeName,
                    SecondarySponsor,
                    ProposalShortTitle,
                    PrincipalInvestigator,
                    ProjectStartDate,
                    ProjectEndDate,
                    InstrumentType,
                    SubContractorName,
                    BudgetProjectStartDate,
                    BudgetProjectEndDate,
                    ProjectAwardedAmount,
                    BudgetStartDate,
                    BudgetEndDate,
                    BudgetFundNumber,
                    ExpenseAmount,
                    IndirectExpenses,
                    DirectExpenses,
                    CashBalance,
                    ReportId,
                    ReportName,
                    REPORTCAT,
                    REPORTSTAT,
                    SubmittedDate,
                    DueDate,
                    StatusDate,
                    CompletedDate,
                    DaysFromDueToCompleted,
                    SubmittedBy,
                    StatusBy,
                    CompletedBy,
                    PeriodStartDate,
                    PeriodEndDate,
                    DeliverableComments,
                    REPORTCAT_CODE,
                    REPORTSTAT_CODE,
                    AgreementId,
                    InstitutionId,
                    DepartmentId,
                    UnitId,
                    SponsorId,
                    PrincipalInvestigatorId,
                    SubcontractsYN,
                    SubContractorId,
                    SubOutContractorId,
                    ContractSpecialistID,
                    ContractSpecialist)
     SELECT    AI.FolderNumber,
               AI.InfoEdPropNumber,
               AI.Institution,
               AI.Department,
               AI.Unit,
               AI.ChiefCode,
               AI.SponsorName,
               AI.SponsorTypeName,
               AI.SecondarySponsor,
               AI.ProposalShortTitle,
               AI.PrincipalInvestigator,
               AI.ProjectStartDate,
               AI.ProjectEndDate,
               AI.InstrumentType,                                
               CASE WHEN DEL.REPORTCAT_CODE IN ('REPORTCAT_13','REPORTCAT_40','REPORTCAT_43') 
                    THEN 'N/A'                -- In
                    ELSE dbo.fnParseSubcontractorName(DEL.ReportName)          -- Out
               END AS SubContractorName,
               BUDG.ProjectStartDate AS BudgetProjectStartDate,
               BUDG.ProjectEndDate AS BudgetProjectEndDate,
               BUDG.ProjectAwardedAmount,
               BUDG.BudgetStartDate,
               BUDG.BudgetEndDate,
               BUDG.FundNumber AS BudgetFundNumber,
               BUDG.ExpenseAmount,
               BUDG.IndirectExpenses,
               BUDG.DirectExpenses,
               -1 * isNull(BUDG.CashBalance,0),
               DEL.ReportId,
               DEL.ReportName,
               DEL.REPORTCAT,
               DEL.REPORTSTAT,
               DEL.SubmittedDate,
               DEL.DueDate,
               DEL.StatusDate,
               DEL.CompletedDate,
               DEL.DaysComplCalc AS DaysFromDueToCompleted,
               ISNULL(DEL.SubmittedBy,'UNASSIGNED') AS Submittedby,
               DEL.StatusBy,
               DEL.CompletedBy,
               DEL.PeriodStartDate,
               DEL.PeriodEndDate,
               DEL.Comments AS DeliverableComments,
               DEL.REPORTCAT_CODE,
               DEL.REPORTSTAT_CODE,
               AI.AgreementId,
               AI.InstitutionId,
               AI.DepartmentId,
               AI.UnitId,
               AI.SponsorId,
               AI.PrincipalInvestigatorId,
               AI.SubcontractsYN,
               AI.SubContractorId,
               NULL,
			   IP.ContractSpecialistID,
               IP.ContractSpecialist 
               
     FROM      dbo.AgrAgreement_Info AI 
               
               -- Security Issues: Not all Users can see all Agreements!
               INNER JOIN #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
			   INNER JOIN #tmpDel DEL ON AI.InfoEdPropNumber = DEL.prop_no
               INNER JOIN InfoEdUDFProposal IP ON AI.InfoEdPropNumber = IP.PropNumber
               LEFT OUTER JOIN
                    -- This function uses the Agreement_Fct table to get Budget and Project data
                    dbo.fnGetFactTableBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId
               
     WHERE     (DEL.DueDate BETWEEN @StartDate AND @EndDate) AND
               (@DeliverableType IS NULL OR DEL.REPORTCAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@DeliverableType))) AND                
               (@DeliverableStatusCodes IS NULL OR DEL.REPORTSTAT_CODE IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@DeliverableStatusCodes))) AND               
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR ISNULL(AI.DepartmentId,-1) IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@ChiefCode IS NULL OR AI.ChiefCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@ChiefCode))) AND     
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND               
               (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor )))-- AND
			   --(@ContractManager IS NULL OR IP.ContractSpecialistID = @ContractManager)) 
               
     ORDER BY  AI.Institution,
               AI.Department,
               AI.Unit,
               AI.FolderNumber,
               DEL.StatusDate
               

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          RAISERROR ('ERROR! Getting Intial Agreement list into TempTable, @Subcontracts',11,1)
          DROP TABLE #tt_Agreements
          RETURN -1
     END
     
     -- Even on Success. Drop now.
     DROP TABLE #tt_Agreements
     
     -- Return Result Set using additional params
     SELECT    FolderNumber,
               InfoEdPropNumber,
               Institution,
               Department,
               Unit,
               ChiefCode,
               SponsorName,
               SponsorTypeName,
               SecondarySponsor,
               ProposalShortTitle,
               PrincipalInvestigator,
               ProjectStartDate,
               ProjectEndDate,
               InstrumentType,
               SubContractorName,
               BudgetProjectStartDate,
               BudgetProjectEndDate,
               ProjectAwardedAmount,
               BudgetStartDate,
               BudgetEndDate,
               BudgetFundNumber,
               ExpenseAmount,
               IndirectExpenses as DirectExpenses,
               DirectExpenses as IndirectExpenses,
               CashBalance,
               ReportId,
               ReportName,
               REPORTCAT,
               REPORTSTAT,
               SubmittedDate,
               DueDate,
               StatusDate,
               CompletedDate,
               DaysFromDueToCompleted,
               SubmittedBy,
               StatusBy,
               CompletedBy,
               PeriodStartDate,
               PeriodEndDate,
               DeliverableComments,
               REPORTCAT_CODE,
               REPORTSTAT_CODE,
               AgreementId,
               InstitutionId,
               DepartmentId,
               UnitId,
               SponsorId,
               PrincipalInvestigatorId,
               SubcontractsYN,
               SubContractorId,
               SubOutContractorId,
			   --ContractSpecialistID,
			   CASE  
				WHEN ContractSpecialistID='' THEN NULL							  
				ELSE ContractSpecialistID   END as ContractSpecialistID,
               ContractSpecialist 
     FROM      @Subcontracts
     WHERE     (@FundNumber IS NULL OR BudgetFundNumber IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@FundNumber))) AND
               
               (@OriginatingSponsor IS NULL OR SecondarySponsor = @OriginatingSponsor ) AND
               
               -- If user is drilling down, this would be passed...
               (@Subcontractor IS NULL OR SubContractorName = @SubContractor) AND
               (@ContractManager IS NULL OR ContractSpecialistID = @ContractManager OR
               (@ContractManager = 'UNASSIGNED' AND (ContractSpecialistID IS NULL OR ContractSpecialistID='') )) AND
               (@SubcontractSpecialist IS NULL OR SubmittedBy = @SubcontractSpecialist OR
               (@SubcontractSpecialist = 'UNASSIGNED' AND SubmittedBy IS NULL )) 
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          RETURN -1
     END

     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptSubContractDeliverableDetailReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubContractDeliverableDetailReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubContractDeliverableDetailReport >>>'
go

GRANT EXECUTE ON dbo.RptSubContractDeliverableDetailReport TO InsightReport,Insight
go
